﻿using MySql.Data.MySqlClient;
using System.Data;

namespace XunChi.DBHelper
{
    internal abstract class MySQLHelper
    {
        /// <summary>
        /// 执行返回第一行第一列的值
        /// </summary>
        /// <param name="conString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string conString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection mySqlCon = new MySqlConnection(conString))
            {
                PrepareCommand(cmd, mySqlCon, null, cmdType, cmdText, cmdParameters);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj;
            }
        }

        /// <summary>
        /// 准备执行命令
        /// </summary>
        /// <param name="cmd">MySqlCommand对象/param>
        /// <param name="mySqlCon">连接MySqlConnection对象</param>
        /// <param name="trans">MySqlTransaction事务对象</param>
        /// <param name="cmdType">Cmd类型，例如存储过程或文本</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdParms">要在命令中使用的MySqlParameters</param>
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection mySqlCon, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            if (mySqlCon.State != ConnectionState.Open) //这里似乎可以改进，因为如果连接已经打开就不必要重新打开数据库连接了。
            {
                mySqlCon.Open();
            }

            cmd.Connection = mySqlCon;
            cmd.CommandText = cmdText;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 根据参数对现有数据库连接执行MSqlCommand（不返回结果集）
        /// </summary>
        /// <remarks>
        /// 例如:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">现有数据库连接</param>
        /// <param name="commandType">CommandType（存储过程、文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>一个int，表示受命令影响的行数</returns>
        public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int affectedRows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return affectedRows;
        }

    }
}
